package com.lanqiao.guimei.dao;

import com.lanqiao.guimei.commons.JdbcUtil;
import com.lanqiao.guimei.vo.TradeDetail;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;


/**
 * @Author:沈林玉
 * @date:2019/8/15 10:28
 * @Description:
 */
public class TradeDetailDao {
    QueryRunner runner = new QueryRunner(true);
    Logger logger = Logger.getLogger(TradeDetailDao.class);

    /**
     * 1.查询月度交易量
     * @param tradeDetail
     * @return
     */
    public List<TradeDetail> selectMonthTradeVolume(TradeDetail tradeDetail){
        String sql = "select to_char(i.trd_time,'mm') trd_month,sum(d.quantity) month_quantity from trade_detail d,trade_info i " +
                "where d.trd_no = i.trd_no group by to_char(i.trd_time,'mm') order by trd_month";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<TradeDetail> monthTradeDetailList = runner.query(conn, sql, new BeanListHandler<>(TradeDetail.class));
            return monthTradeDetailList;
        } catch (SQLException e) {
            logger.error("查询月季交易量失败。。。"+e.getMessage());
        }
        return null;
    }
    /**
     * 2.查询季度交易量
     * @param tradeDetail
     * @return
     */
    public List<TradeDetail> selectQuarterTradeVolume(TradeDetail tradeDetail){
        String sql = "select to_char(i.trd_time,'q') trd_quarter,sum(quantity) quarter_quantity from trade_detail d,trade_info i " +
                "where d.trd_no = i.trd_no group by to_char(i.trd_time,'q') order by trd_quarter";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<TradeDetail> quarterTradeDetailList = runner.query(conn, sql, new BeanListHandler<>(TradeDetail.class));
            return quarterTradeDetailList;
        } catch (SQLException e) {
            logger.error("查询季度交易量失败。。。"+e.getMessage());
        }
        return null;
    }
    /**
     * 3.查询年度交易量
     * @param tradeDetail
     * @return
     */
    public List<TradeDetail> selectYearTradeVolume(TradeDetail tradeDetail){
        String sql = "select to_char(i.trd_time,'yyyy')trd_year,sum(quantity) year_quantity from trade_detail d,trade_info i " +
                "where d.trd_no = i.trd_no group by to_char(i.trd_time,'yyyy') order by trd_year";
        try (Connection conn = JdbcUtil.getConnection()) {
            List<TradeDetail> yearTradeDetailList = runner.query(conn, sql, new BeanListHandler<>(TradeDetail.class));
            return yearTradeDetailList;
        } catch (SQLException e) {
            logger.error("查询年度交易量失败。。。"+e.getMessage());
        }
        return null;
    }

    public static void main(String[] args) {
        TradeDetailDao dao = new TradeDetailDao();
       /* List<TradeDetail> tradeDetails = dao.selectMonthTradeVolume(new TradeDetail());
        for (TradeDetail tradeDetail1:tradeDetails){
            System.out.println(tradeDetail1);
        }*/
        /*List<TradeDetail> tradeDetails = dao.selectQuarterTradeVolume(new TradeDetail());
        for (TradeDetail tradeDetail1:tradeDetails){
            System.out.println(tradeDetail1);
        }*/
        List<TradeDetail> tradeDetails = dao.selectYearTradeVolume(new TradeDetail());
        for (TradeDetail tradeDetail1:tradeDetails){
            System.out.println(tradeDetail1);
        }

    }

}
